package com.example.util;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.example.common.ConstantConfig;
import com.example.common.ObjectUtils;

@Component
public class ExportExcelUtil {
	private static final Logger LOG = LoggerFactory.getLogger(ExportExcelUtil.class);
	
	@Autowired
	private ConstantConfig constantConfig;
	
	public  String createExcel(String title,List<String> headNames, List<String> keys,List<Map> valueList, String path) throws IOException{
		// 第一步，创建一个webbook，对应一个Excel文件  
		HSSFWorkbook wb = new HSSFWorkbook(); 
		// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
		HSSFSheet sheet = wb.createSheet(title);  
		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
        HSSFRow row = sheet.createRow((int) 0);  
        // 第四步，创建单元格，并设置值表头 设置表头居中  
        HSSFCellStyle style = wb.createCellStyle(); 
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
        
        HSSFCell cell = row.createCell(0);
        for(int i = 0; i < headNames.size(); i++){
        	 cell = row.createCell(i);
        	 cell.setCellValue(headNames.get(i));  
             cell.setCellStyle(style);  
        }
        
        for(int i = 0; i < valueList.size(); i++){
        	Map map = valueList.get(i);
        	row = sheet.createRow((int) i + 1);  
        	 for(int t = 0; t < keys.size(); t++){
        		 row.createCell(t).setCellValue((map.containsKey(keys.get(t)) && !ObjectUtils.isEmpty(map.get(keys.get(t))))? map.get(keys.get(t)).toString(): "");
        	 }
        }
        
        FileOutputStream fout = null;
        String trueFileName = null;
        try {
        	if(ObjectUtils.isEmpty(path)){
        		trueFileName = String.valueOf(System.currentTimeMillis()) + ".xls"; 
            	fout = new FileOutputStream(constantConfig.getConfig("excelPath")+ trueFileName);  
        	}else{
        		fout = new FileOutputStream(path); 
        	}
        	
			wb.write(fout);

		} catch (IOException e) {
			LOG.info("写入Excel异常:" + e.getMessage());
		} finally{
			if(fout != null){
				fout.close();
			}
			
			if(wb != null){
				wb.close();
			}
	
		}
       return constantConfig.getConfig("excelDown") + trueFileName;
        
	}
	
	
	/**
	 * 创建多个sheet的excel
	 * 
	 * @author LF
	 * @Date: 2017年9月18日 上午10:30:51
	 * @param sheetCount
	 * @param titles
	 * @param headNames
	 * @param keys
	 * @param valueList
	 * @return
	 * @throws IOException
	 */
	public  String createExcelWithSheets(Integer sheetCount, List<String> titles, Map<String, List<String>> headNames, Map<String, List<String>> keys, Map<String, List<Map>> valueList) throws IOException{
		// 第一步，创建一个webbook，对应一个Excel文件  
		HSSFWorkbook wb = new HSSFWorkbook(); 
		for (int i = 0; i < sheetCount; i++) {
			String sheetKey = "sheet" + (i + 1);
			// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
			HSSFSheet sheet = wb.createSheet(titles.get(i));  
			// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
			HSSFRow row = sheet.createRow((int) 0);  
			// 第四步，创建单元格，并设置值表头 设置表头居中  
			HSSFCellStyle style = wb.createCellStyle(); 
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
			
			HSSFCell cell = row.createCell(0);
			
			List<String> headNameList = headNames.get(sheetKey);
			for(int j = 0; j < headNameList.size(); j++){
				cell = row.createCell(j);
				cell.setCellValue(headNameList.get(j));  
				cell.setCellStyle(style);  
			}
			
			List<Map> values = valueList.get(sheetKey);
			for(int j = 0; j < values.size(); j++){
				Map map = values.get(j);
				row = sheet.createRow((int) j + 1);
				List<String> keyList = keys.get(sheetKey);
				for(int t = 0; t < keyList.size(); t++){
					row.createCell(t).setCellValue(map.containsKey(keyList.get(t))? map.get(keyList.get(t)).toString(): "");
				}
			}
		}
        
        FileOutputStream fout = null;
        String trueFileName = null;
        try {
        	trueFileName = String.valueOf(System.currentTimeMillis()) + ".xls"; 
        	fout = new FileOutputStream(constantConfig.getConfig("excelPath") + trueFileName);  
			wb.write(fout);

		} catch (IOException e) {
			LOG.info("写入Excel异常:" + e.getMessage());
		} finally{
			if(fout != null){
				fout.close();
			}
			
			if(wb != null){
				wb.close();
			}
	
		}
       return constantConfig.getConfig("excelDown") + trueFileName;
        
	}
	
	/**
	 * 本地生成Excel,返回本地地址
	 * 
	 * @param title
	 * @param headNames
	 * @param keys
	 * @param valueList
	 * @param path
	 * @return
	 * @throws IOException
	 */
	public  String createExcelFile(String title,List<String> headNames, List<String> keys,List<Map> valueList, String path) throws IOException{
		// 第一步，创建一个webbook，对应一个Excel文件  
		HSSFWorkbook wb = new HSSFWorkbook(); 
		// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
		HSSFSheet sheet = wb.createSheet(title);  
		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
        HSSFRow row = sheet.createRow((int) 0);  
        // 第四步，创建单元格，并设置值表头 设置表头居中  
        HSSFCellStyle style = wb.createCellStyle(); 
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
        
        HSSFCell cell = row.createCell(0);
        for(int i = 0; i < headNames.size(); i++){
        	 cell = row.createCell(i);
        	 cell.setCellValue(headNames.get(i));  
             cell.setCellStyle(style);  
        }
        
        for(int i = 0; i < valueList.size(); i++){
        	Map map = valueList.get(i);
        	row = sheet.createRow((int) i + 1);  
        	 for(int t = 0; t < keys.size(); t++){
        		 row.createCell(t).setCellValue((map.containsKey(keys.get(t)) && !ObjectUtils.isEmpty(map.get(keys.get(t))))? map.get(keys.get(t)).toString(): "");
        	 }
        }
        
        FileOutputStream fout = null;
        String trueFileName = null;
        try {
        	if(ObjectUtils.isEmpty(path)){
        		trueFileName = String.valueOf(System.currentTimeMillis()) + ".xls"; 
            	fout = new FileOutputStream(constantConfig.getConfig("excelPath")+ trueFileName);  
        	}else{
        		fout = new FileOutputStream(path); 
        	}
        	
			wb.write(fout);

		} catch (IOException e) {
			LOG.info("写入Excel异常:" + e.getMessage());
		} finally{
			if(fout != null){
				fout.close();
			}
			
			if(wb != null){
				wb.close();
			}
	
		}
       return  constantConfig.getConfig("excelPath") + trueFileName;
	}
}
